SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_VALIDATE_USER](@LOGIN VARCHAR(25),@PASS VARCHAR(25)) AS

DECLARE @PASS_AUX VARCHAR(25)

OPEN SYMMETRIC KEY NDEV_PASSWORD DECRYPTION
BY CERTIFICATE NDEV
SET @PASS_AUX = (SELECT CONVERT(VARCHAR(250),DECRYPTBYKEY(USER_PASSWORD)) AS USER_PASSWORD FROM [dbo].[T_USER] WHERE @LOGIN=USER_LOGIN)
CLOSE SYMMETRIC KEY NDEV_PASSWORD

IF (@PASS_AUX=@PASS)
BEGIN
DECLARE @ROLE INT
SET @ROLE=(SELECT TOP 1 RO.ROLE_ID 
		   FROM T_USER U 
		   INNER JOIN T_USER_ROLE R ON R.PERSON_ID=U.PERSON_ID
		   INNER JOIN T_ROLE RO ON RO.ROLE_ID=R.ROLE_ID
		   WHERE U.USER_LOGIN =@LOGIN)
SELECT 'SUCCESS'
RETURN @ROLE
END
ELSE 
BEGIN
SELECT 'FAILED'
RETURN 0
END
GO
